package com.adds.lvds.core.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**excel导出工具类
 * @author zhangjian
 *
 */
@SuppressWarnings("all")
public class ExcelUtil {

	private static final String ISTRUE = "是";
	private static final String ISFALSE = "否";
	 
	/**创建特定字号，且水平、垂直居中、字体加粗的样式。
	 * @param workbook
	 * @param fontSize
	 * @return
	 */
	public static HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize) {
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		//cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		//cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		//cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		//cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框   
		HSSFFont font1 = workbook.createFont();
		font1.setFontName("宋体");
	    //font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
		font1.setFontHeightInPoints(fontSize);// 字体大小
		cellStyle.setFont(font1);// 设置字体
		return cellStyle;
	}

	/**
	 * 导出Excel
	 * @param sheetTitle 工作表主标题
	 * @param titles excel中文列标题
	 * @param files 各个列标题对应的实体变量
	 * @param objectList 要导出的实体类的集合
	 * @param outputStream 输出流
	 * @throws IOException
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws InvocationTargetException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InstantiationException
	 */
	
	public static void exportExcel(String sheetTitle, String[] titles, String[] files, List objectList, Map<Object, String> rulesMap, OutputStream outputStream) throws IOException, SecurityException,
			NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException, InstantiationException {
		// 创建excel
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 第一行头标题样式
		HSSFCellStyle style1 = createStyle(workbook, (short) 16);
		HSSFCellStyle style2 = createStyle(workbook, (short) 13);
		//创建工作表
         HSSFSheet sheet = workbook.createSheet(sheetTitle);
         sheet.setDefaultColumnWidth(20);// 设置列宽
		// 创建第一行表头，标题栏
		HSSFRow row1 = sheet.createRow(0);
		CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, (files.length-1));
		sheet.addMergedRegion(rangeAddress);
		HSSFCell cell1 = row1.createCell(0);// 创建单元格
		cell1.setCellStyle(style1);// 设置标题栏样式
		cell1.setCellValue(sheetTitle);// 设置主标题
		HSSFRow row2 = sheet.createRow(1);

		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell2 = row2.createCell(i);
			cell2.setCellStyle(style2);
			cell2.setCellValue(titles[i]);
		}
		// 2.3 创建数据行和单元格，写入数据
		int index = 2;
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		HSSFRow row = null;
		Method method = null;
		HSSFCell cell11 = null;
		String fileValue = "";
		Object fileObject = null;
		for (Object obj : objectList) {
			row = sheet.createRow(index++);
			for (int i = 0; i < files.length; i++) {
				method = obj.getClass().getMethod("get" + files[i]);
				cell11 = row.createCell(i);
				fileObject = method.invoke(obj);// 通过反射getter方法获取值
				if (fileObject != null) {//判断属性值是否为null，是的话显示空值
					if (fileObject instanceof Date) {
						fileValue = dateFormat.format((Date) fileObject);
					}else if (fileObject instanceof Boolean) {
						if((boolean) fileObject){
							fileValue =ISTRUE + "";}
						if(!(boolean) fileObject){
							fileValue =ISFALSE + "";}
					}else {// 其它类型的按照字符串处理
						fileValue = fileObject + "";
					}
					if(rulesMap.containsKey(files[i])){
						String [] rules = rulesMap.get(files[i]).split(",");//按照 , 将字符串分成数组
						for(String str: rules){
							if(str.contains(fileValue)){
								fileValue = str.split("@")[1];
								break;
							}
						}
					}
				} else {fileValue = "";}
				cell11.setCellValue(fileValue);
			}
		}
		workbook.write(outputStream);
		outputStream.close();
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		Timestamp now = new Timestamp(System.currentTimeMillis());
		List<Object> userList = new ArrayList<Object>();
		String[] titleFiles = { "Name", "Dept", "Role", "Account", "Password","Gender", "Birthday" };
		String[] titles = { "用户名", "部门", "角色", "帐号", "密码","性别", "生日", };
		FileOutputStream outputStream = new FileOutputStream("D:\\test.xls");
		HashMap<Object, String> rulesMap = new HashMap<Object, String>();
		rulesMap.put(5, "true@男,false@女");
		//rulesMap key为7 是状态属性对应的titleFiles的数组索引，rulesMap对应的value，设置了自定义规则：真值@显示值,真值@显示值
		rulesMap.put(7, "1@有效,0@无效");
		exportExcel("用户列表记录", titles, titleFiles, userList, rulesMap, outputStream);
	}

}
